{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bcdaf55c",
   "metadata": {},
   "source": [
    "## Storing data on disk with SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61b6206c",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install exchange-calendars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "746d2538",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f3bbaa88",
   "metadata": {},
   "outputs": [],
   "source": [
    "import exchange_calendars as xcals\n",
    "import pandas as pd\n",
    "from IPython.display import Markdown, display\n",
    "from openbb import obb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cb3f70cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "obb.user.preferences.output_type = \"dataframe\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "048deab5",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "source": [
    "Function to fetch historical stock data for a given symbol and date range, and add a 'symbol' column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "81febbfd",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "def get_stock_data(symbol, start_date=None, end_date=None):\n",
    "    data = obb.equity.price.historical(\n",
    "        symbol,\n",
    "        start_date=start_date,\n",
    "        end_date=end_date,\n",
    "        provider=\"yfinance\",\n",
    "    )\n",
    "    data.reset_index(inplace=True)\n",
    "    data[\"symbol\"] = symbol\n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "913b8d8d",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "source": [
    "Function to save the fetched stock data to an SQLite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "695f0cd7",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "def save_data_range(symbol, conn, start_date=None, end_date=None):\n",
    "    data = get_stock_data(symbol, start_date, end_date)\n",
    "    data.to_sql(\"stock_data\", conn, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa18fb90",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "source": [
    "Function to save the stock data for the last trading session to an SQLite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5c060096",
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_last_trading_session(symbol, conn):\n",
    "    today = pd.Timestamp.today()\n",
    "    data = get_stock_data(symbol, today, today)\n",
    "    data.to_sql(\"stock_data\", conn, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd6ae005",
   "metadata": {},
   "source": [
    "Establish a connection to the SQLite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "14f64ffc",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect(\"market_data.sqlite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09989016",
   "metadata": {},
   "source": [
    "Save data for multiple stock symbols in the specified date range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ba511248",
   "metadata": {},
   "outputs": [],
   "source": [
    "for symbol in [\"SPY\", \"QQQ\", \"DIA\"]:\n",
    "    save_data_range(symbol, conn=conn, start_date=\"2020-06-01\", end_date=\"2023-01-01\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4729b3d1",
   "metadata": {},
   "source": [
    "Read and display data for the stock symbol \"SPY\" from the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4a4c5b51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>split_ratio</th>\n",
       "      <th>dividend</th>\n",
       "      <th>capital_gains</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>303.619995</td>\n",
       "      <td>306.209991</td>\n",
       "      <td>303.059998</td>\n",
       "      <td>305.549988</td>\n",
       "      <td>55758300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-06-02</td>\n",
       "      <td>306.549988</td>\n",
       "      <td>308.130005</td>\n",
       "      <td>305.100006</td>\n",
       "      <td>308.079987</td>\n",
       "      <td>74267200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-06-03</td>\n",
       "      <td>310.239990</td>\n",
       "      <td>313.220001</td>\n",
       "      <td>309.940002</td>\n",
       "      <td>312.179993</td>\n",
       "      <td>92567600</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-06-04</td>\n",
       "      <td>311.109985</td>\n",
       "      <td>313.000000</td>\n",
       "      <td>309.079987</td>\n",
       "      <td>311.359985</td>\n",
       "      <td>75794400</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>317.230011</td>\n",
       "      <td>321.269989</td>\n",
       "      <td>317.160004</td>\n",
       "      <td>319.339996</td>\n",
       "      <td>150524700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1301</th>\n",
       "      <td>2022-12-23</td>\n",
       "      <td>379.649994</td>\n",
       "      <td>383.059998</td>\n",
       "      <td>378.029999</td>\n",
       "      <td>382.910004</td>\n",
       "      <td>59857300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1302</th>\n",
       "      <td>2022-12-27</td>\n",
       "      <td>382.790009</td>\n",
       "      <td>383.149994</td>\n",
       "      <td>379.649994</td>\n",
       "      <td>381.399994</td>\n",
       "      <td>51638200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1303</th>\n",
       "      <td>2022-12-28</td>\n",
       "      <td>381.329987</td>\n",
       "      <td>383.390015</td>\n",
       "      <td>376.420013</td>\n",
       "      <td>376.660004</td>\n",
       "      <td>70911500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1304</th>\n",
       "      <td>2022-12-29</td>\n",
       "      <td>379.630005</td>\n",
       "      <td>384.350006</td>\n",
       "      <td>379.079987</td>\n",
       "      <td>383.440002</td>\n",
       "      <td>66970900</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1305</th>\n",
       "      <td>2022-12-30</td>\n",
       "      <td>380.640015</td>\n",
       "      <td>382.579987</td>\n",
       "      <td>378.429993</td>\n",
       "      <td>382.429993</td>\n",
       "      <td>84022200</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1306 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            date        open        high         low       close     volume  \\\n",
       "0     2020-06-01  303.619995  306.209991  303.059998  305.549988   55758300   \n",
       "1     2020-06-02  306.549988  308.130005  305.100006  308.079987   74267200   \n",
       "2     2020-06-03  310.239990  313.220001  309.940002  312.179993   92567600   \n",
       "3     2020-06-04  311.109985  313.000000  309.079987  311.359985   75794400   \n",
       "4     2020-06-05  317.230011  321.269989  317.160004  319.339996  150524700   \n",
       "...          ...         ...         ...         ...         ...        ...   \n",
       "1301  2022-12-23  379.649994  383.059998  378.029999  382.910004   59857300   \n",
       "1302  2022-12-27  382.790009  383.149994  379.649994  381.399994   51638200   \n",
       "1303  2022-12-28  381.329987  383.390015  376.420013  376.660004   70911500   \n",
       "1304  2022-12-29  379.630005  384.350006  379.079987  383.440002   66970900   \n",
       "1305  2022-12-30  380.640015  382.579987  378.429993  382.429993   84022200   \n",
       "\n",
       "      split_ratio  dividend  capital_gains symbol  \n",
       "0             0.0       0.0            0.0    SPY  \n",
       "1             0.0       0.0            0.0    SPY  \n",
       "2             0.0       0.0            0.0    SPY  \n",
       "3             0.0       0.0            0.0    SPY  \n",
       "4             0.0       0.0            0.0    SPY  \n",
       "...           ...       ...            ...    ...  \n",
       "1301          0.0       0.0            0.0    SPY  \n",
       "1302          0.0       0.0            0.0    SPY  \n",
       "1303          0.0       0.0            0.0    SPY  \n",
       "1304          0.0       0.0            0.0    SPY  \n",
       "1305          0.0       0.0            0.0    SPY  \n",
       "\n",
       "[1306 rows x 10 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_1 = pd.read_sql_query(\"SELECT * from stock_data where symbol='SPY'\", conn)\n",
    "display(df_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3dbdfee",
   "metadata": {},
   "source": [
    "Read and display data for \"SPY\" where the volume is greater than 100,000,000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7c2fdc45",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>split_ratio</th>\n",
       "      <th>dividend</th>\n",
       "      <th>capital_gains</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-05</td>\n",
       "      <td>317.230011</td>\n",
       "      <td>321.269989</td>\n",
       "      <td>317.160004</td>\n",
       "      <td>319.339996</td>\n",
       "      <td>150524700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-06-11</td>\n",
       "      <td>311.459991</td>\n",
       "      <td>312.149994</td>\n",
       "      <td>300.010010</td>\n",
       "      <td>300.609985</td>\n",
       "      <td>209243600</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-06-12</td>\n",
       "      <td>308.239990</td>\n",
       "      <td>309.079987</td>\n",
       "      <td>298.600006</td>\n",
       "      <td>304.209991</td>\n",
       "      <td>194678900</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-06-15</td>\n",
       "      <td>298.019989</td>\n",
       "      <td>308.279999</td>\n",
       "      <td>296.739990</td>\n",
       "      <td>307.049988</td>\n",
       "      <td>135782700</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-06-16</td>\n",
       "      <td>315.480011</td>\n",
       "      <td>315.640015</td>\n",
       "      <td>307.670013</td>\n",
       "      <td>312.959991</td>\n",
       "      <td>137627500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>2022-12-13</td>\n",
       "      <td>410.220001</td>\n",
       "      <td>410.489990</td>\n",
       "      <td>399.070007</td>\n",
       "      <td>401.970001</td>\n",
       "      <td>123782500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>2022-12-14</td>\n",
       "      <td>401.609985</td>\n",
       "      <td>405.500000</td>\n",
       "      <td>396.309998</td>\n",
       "      <td>399.399994</td>\n",
       "      <td>108111300</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>305</th>\n",
       "      <td>2022-12-15</td>\n",
       "      <td>394.299988</td>\n",
       "      <td>395.250000</td>\n",
       "      <td>387.890015</td>\n",
       "      <td>389.630005</td>\n",
       "      <td>117705900</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>306</th>\n",
       "      <td>2022-12-16</td>\n",
       "      <td>385.179993</td>\n",
       "      <td>386.579987</td>\n",
       "      <td>381.040009</td>\n",
       "      <td>383.269989</td>\n",
       "      <td>119858000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.781</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>307</th>\n",
       "      <td>2022-12-22</td>\n",
       "      <td>383.049988</td>\n",
       "      <td>386.209991</td>\n",
       "      <td>374.769989</td>\n",
       "      <td>380.720001</td>\n",
       "      <td>100120900</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SPY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>308 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           date        open        high         low       close     volume  \\\n",
       "0    2020-06-05  317.230011  321.269989  317.160004  319.339996  150524700   \n",
       "1    2020-06-11  311.459991  312.149994  300.010010  300.609985  209243600   \n",
       "2    2020-06-12  308.239990  309.079987  298.600006  304.209991  194678900   \n",
       "3    2020-06-15  298.019989  308.279999  296.739990  307.049988  135782700   \n",
       "4    2020-06-16  315.480011  315.640015  307.670013  312.959991  137627500   \n",
       "..          ...         ...         ...         ...         ...        ...   \n",
       "303  2022-12-13  410.220001  410.489990  399.070007  401.970001  123782500   \n",
       "304  2022-12-14  401.609985  405.500000  396.309998  399.399994  108111300   \n",
       "305  2022-12-15  394.299988  395.250000  387.890015  389.630005  117705900   \n",
       "306  2022-12-16  385.179993  386.579987  381.040009  383.269989  119858000   \n",
       "307  2022-12-22  383.049988  386.209991  374.769989  380.720001  100120900   \n",
       "\n",
       "     split_ratio  dividend  capital_gains symbol  \n",
       "0            0.0     0.000            0.0    SPY  \n",
       "1            0.0     0.000            0.0    SPY  \n",
       "2            0.0     0.000            0.0    SPY  \n",
       "3            0.0     0.000            0.0    SPY  \n",
       "4            0.0     0.000            0.0    SPY  \n",
       "..           ...       ...            ...    ...  \n",
       "303          0.0     0.000            0.0    SPY  \n",
       "304          0.0     0.000            0.0    SPY  \n",
       "305          0.0     0.000            0.0    SPY  \n",
       "306          0.0     1.781            0.0    SPY  \n",
       "307          0.0     0.000            0.0    SPY  \n",
       "\n",
       "[308 rows x 10 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_2 = pd.read_sql_query(\n",
    "    \"SELECT * from stock_data where symbol='SPY' and volume > 100000000\", conn\n",
    ")\n",
    "display(df_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eea774c6",
   "metadata": {},
   "source": [
    "**Jason Strimpel** is the founder of <a href='https://pyquantnews.com/'>PyQuant News</a> and co-founder of <a href='https://www.tradeblotter.io/'>Trade Blotter</a>. His career in algorithmic trading spans 20+ years. He previously traded for a Chicago-based hedge fund, was a risk manager at JPMorgan, and managed production risk technology for an energy derivatives trading firm in London. In Singapore, he served as APAC CIO for an agricultural trading firm and built the data science team for a global metals trading firm. Jason holds degrees in Finance and Economics and a Master's in Quantitative Finance from the Illinois Institute of Technology. His career spans America, Europe, and Asia. He shares his expertise through the <a href='https://pyquantnews.com/subscribe-to-the-pyquant-newsletter/'>PyQuant Newsletter</a>, social media, and has taught over 1,000+ algorithmic trading with Python in his popular course **<a href='https://gettingstartedwithpythonforquantfinance.com/'>Getting Started With Python for Quant Finance</a>**. All code is for educational purposes only. Nothing provided here is financial advise. Use at your own risk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ead5f1b9-34ec-44f3-a93e-e64210bd6673",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
